package com.cttic.erp.mapper;

import java.math.BigDecimal;
import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.cttic.erp.bean.InfoEmployeeBean;
import com.cttic.erp.bean.InfoProjectEmployeeBean;

public interface InfoProjectEmployeeMapper {

	@Select("select * from info_project_employee")
	public List<InfoProjectEmployeeBean> getBeans();
	
	@Select("select a.id, a.prj_id, a.employee_id, a.state, b.prj_name, c.employee_name "
			+ "from info_project_employee a, info_project b, info_employee c"
			+ " where a.prj_id = b.prj_id and a.employee_id = c.employee_id"
			+ "   and a.prj_id = #{prj_id}")
	public List<InfoProjectEmployeeBean> getBeansByPrjId(@Param("prj_id") BigDecimal prj_id);
	
	@Select("select a.id, a.prj_id, a.employee_id, b.prj_name, c.employee_name "
			+ " from info_project_employee a, info_project b, info_employee c "
			+ "where a.prj_id = b.prj_id and a.employee_id = c.employee_id"
			+ "  and a.prj_id = #{prj_id} and a.state = '0'")
	public List<InfoProjectEmployeeBean> getValidBeansByPrjId(@Param("prj_id") BigDecimal prj_id);
	
	@Select("select * from info_project_employee where state = #{state}")
	public List<InfoProjectEmployeeBean> getBeansByState(@Param("state") String state);
	
	@Insert("insert into info_project_employee(prj_id, employee_id, state)"
			+ " VALUES(#{bean.prj_id}, #{bean.employee_id}, '0')")
	public void insert(@Param("bean") InfoProjectEmployeeBean bean);
	
	@Update("update info_project_employee set prj_id = #{bean.prj_id}, employee_id = #{bean.employee_id}"
			+ ", state = #{bean.state} where id = #{bean.id}")
	public void update(@Param("bean") InfoProjectEmployeeBean bean);
	
	@Select("select * from info_employee where employee_id not in(select employee_id from info_project_employee where state = '0' and prj_id = #{prj_id})")
	public List<InfoEmployeeBean> getNoPrjEmployee(@Param("prj_id") BigDecimal prj_id);
	
	@Select("select distinct d.employee_id, d.employee_name"
		  + "  from info_project a,"
		  + "       busi_weekly_report_record b,"
		  + "       busi_weekly_report_result c,"
		  + "       info_employee d"
		  + " where c.result_type = 'TASK' and c.result_code = 'PRJ_ID'"
		  + "   and a.prj_id = c.result_value"
		  + "   and b.record_id = c.record_id"
		  + "   and b.employee_id = d.employee_id"
		  + "   and a.manager_employee_id = #{managerEmployeeId}")
	public List<InfoProjectEmployeeBean> getBeansByManagerEmployeeId(@Param("managerEmployeeId") long managerEmployeeId);

	@Select("select distinct d.employee_id, d.employee_name"
			  + "  from info_project a,"
			  + "       busi_weekly_report_record b,"
			  + "       busi_weekly_report_result c,"
			  + "       info_employee d"
			  + " where c.result_type = 'TASK' and c.result_code = 'PRJ_ID'"
			  + "   and a.prj_id = c.result_value"
			  + "   and b.record_id = c.record_id"
			  + "   and b.employee_id = d.employee_id"
			  + "   and a.manager_employee_id = #{managerEmployeeId}"
			  + "   and a.prj_id = #{prjId}")
		public List<InfoProjectEmployeeBean> getBeansByManagerEmployeeIdAndPrj(@Param("managerEmployeeId") long managerEmployeeId, @Param("prjId") long prjId);

	@Select("select distinct d.employee_id, d.employee_name"
			  + "  from busi_weekly_report_record b,"
			  + "       busi_weekly_report_result c,"
			  + "       info_employee d"
			  + " where c.result_code = 'PRJ_ID'"
			  + "   and b.record_id = c.record_id"
			  + "   and b.employee_id = d.employee_id"
			  + "   and c.result_value = #{prjId}")
		public List<InfoProjectEmployeeBean> getBeansByPrj(@Param("prjId") long prjId);

}
